package com.cqeec.myblog.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.cqeec.myblog.model.Article;
import com.cqeec.myblog.util.DbUtil;

/**
 * 针对文章表进行读写数据的接口的实现类。
 * 
 * @author TLF。
 *
 */
public class ArticleDaoImpl implements ArticleDao {

	@Override
	public List<Article> select() {
		List<Article> list = null;
		try {
			// 通过数据库工具类获取数据库连接对象
			Connection conn = DbUtil.getDbConnection();
			// 通过数据库连接对象创建SQL语句对象：Statement或PreparedStatement
			// Statement语句对象的创建： Statement state=conn.createStatement();
			// PreparedStatement语句对象的创建：PreparedStatement ps=conn.prepareStatement("SQL语句");
			Statement state = conn.createStatement();
			// 通过语句对象执行SQL语句
			// 执行查询语句：ResultSet rs=state.executeQuery("SQL查询语句"); //SELECT
			// 执行数据操作语句：state.executeUpdate("SQL操作语句"); //INSERT、UPDATE、DELETE
			ResultSet rs = state.executeQuery("SELECT * FROM Article");
			// 遍历数据结果集将数据结果中的每条记录转为Java类对象(Model)
			list = new ArrayList<Article>();
			Article article;
			while (rs.next()) {
				article = new Article(); // 一条记录对应一个对象
				// 将记录中字段的值对应的存入对象属性中
				article.setId(rs.getString("id"));
				article.setBlogger(rs.getString("blogger"));
				article.setTitle(rs.getString("title"));
				article.setContent(rs.getString("content"));
				article.setCreationTime(rs.getDate("creationTime"));
				article.setIsReleased(rs.getBoolean("isReleased"));
				article.setReleaseTime(rs.getDate("releaseTime"));
				list.add(article); // 将对象添加列表中
			}
			rs.close();
			state.close();
		} catch (Exception e) {
			list = null;
			e.printStackTrace();
		}
		return (list == null || list.isEmpty()) ? null : list;
	}

	@Override
	public void insert(Article article) throws ClassNotFoundException, SQLException {
		Connection conn = DbUtil.getDbConnection();
		PreparedStatement ps = conn.prepareStatement(
				"INSERT INTO Article(id,blogger,type,title,content,creationTime,isReleased) VALUES(?,?,?,?,?,?,?)");
		ps.setString(1, article.getId());
		ps.setString(2, article.getBlogger());
		ps.setString(3, article.getType());
		ps.setString(4, article.getTitle());
		ps.setString(5, article.getContent());
		java.util.Date creationTime = article.getCreationTime();
		ps.setDate(6, creationTime == null ? null : new java.sql.Date(creationTime.getTime()));
		ps.setBoolean(7, article.getIsReleased());
		ps.executeUpdate();
	}

	@Override
	public List<Article> findByType(String type) throws ClassNotFoundException, SQLException {
		Connection conn = DbUtil.getDbConnection();
		Statement state = conn.createStatement();
		ResultSet rs = state.executeQuery("SELECT * FROM Article WHERE type='" + type + "'");
		List<Article> list = new ArrayList<Article>();
		Article article;
		while (rs.next()) {
			article = new Article(); // 一条记录对应一个对象
			// 将记录中字段的值对应的存入对象属性中
			article.setId(rs.getString("id"));
			article.setBlogger(rs.getString("blogger"));
			article.setTitle(rs.getString("title"));
			article.setContent(rs.getString("content"));
			article.setCreationTime(rs.getDate("creationTime"));
			article.setIsReleased(rs.getBoolean("isReleased"));
			article.setReleaseTime(rs.getDate("releaseTime"));
			list.add(article); // 将对象添加列表中
		}
		rs.close();
		state.close();
		return list;
	}

	@Override
	public List<Article> findByPage(int pageSize, int pageNumber) throws ClassNotFoundException, SQLException {
		List<Article> list = new ArrayList<Article>();
		Connection conn = DbUtil.getDbConnection();
		Statement state = conn.createStatement();
//		ResultSet rs = state.executeQuery("SELECT TOP " + pageSize + " * FROM Article " + " WHERE id NOT IN(SELECT TOP "
//				+ (pageSize * (pageNumber - 1)) + " id FROM Article)");

		ResultSet rs = state
				.executeQuery("SELECT * FROM Article LIMIT " + (pageSize * (pageNumber - 1)) + "," + pageSize);
		Article article;
		while (rs.next()) {
			article = new Article(); // 一条记录对应一个对象
			// 将记录中字段的值对应的存入对象属性中
			article.setId(rs.getString("id"));
			article.setBlogger(rs.getString("blogger"));
			article.setTitle(rs.getString("title"));
			article.setContent(rs.getString("content"));
			article.setCreationTime(rs.getDate("creationTime"));
			article.setIsReleased(rs.getBoolean("isReleased"));
			article.setReleaseTime(rs.getDate("releaseTime"));
			list.add(article); // 将对象添加列表中
		}
		rs.close();
		state.close();
		return list;
	}

	@Override
	public int count() throws ClassNotFoundException, SQLException {
		Connection conn = DbUtil.getDbConnection();
		Statement state = conn.createStatement();
		ResultSet rs = state.executeQuery("SELECT COUNT(*) FROM Article");
		rs.next();
		int result = rs.getInt(1);
		rs.close();
		state.close();
		return result;
	}

	@Override
	public int count(String type) throws ClassNotFoundException, SQLException {
		Connection conn = DbUtil.getDbConnection();
		Statement state = conn.createStatement();
		ResultSet rs = state.executeQuery("SELECT COUNT(*) FROM Article WHERE type='" + type + "'");
		rs.next();
		int result = rs.getInt(1);
		rs.close();
		state.close();
		return result;
	}

	@Override
	public List<Article> findByPage(int pageSize, int pageNumber, String type)
			throws ClassNotFoundException, SQLException {
		List<Article> list = new ArrayList<Article>();
		Connection conn = DbUtil.getDbConnection();
		Statement state = conn.createStatement();
//		ResultSet rs = state.executeQuery("SELECT TOP " + pageSize + " * FROM Article " + " WHERE id NOT IN(SELECT TOP "
//				+ (pageSize * (pageNumber - 1)) + " id FROM Article WHERE type='" + type + "') AND type='" + type
//				+ "'");
		ResultSet rs = state.executeQuery("SELECT * FROM Article WHERE type='" + type + "' LIMIT "
				+ (pageSize * (pageNumber - 1)) + "," + pageSize);
		Article article;
		while (rs.next()) {
			article = new Article(); // 一条记录对应一个对象
			// 将记录中字段的值对应的存入对象属性中
			article.setId(rs.getString("id"));
			article.setBlogger(rs.getString("blogger"));
			article.setTitle(rs.getString("title"));
			article.setContent(rs.getString("content"));
			article.setCreationTime(rs.getDate("creationTime"));
			article.setIsReleased(rs.getBoolean("isReleased"));
			article.setReleaseTime(rs.getDate("releaseTime"));
			list.add(article); // 将对象添加列表中
		}
		rs.close();
		state.close();
		return list;
	}

	@Override
	public Article findById(String id) throws ClassNotFoundException, SQLException {
		Connection conn = DbUtil.getDbConnection();
		Statement state = conn.createStatement();
		ResultSet rs = state.executeQuery("SELECT * FROM Article WHERE id='" + id + "'");
		Article article = null;
		if (rs.next()) {
			article = new Article(); // 一条记录对应一个对象
			// 将记录中字段的值对应的存入对象属性中
			article.setId(rs.getString("id"));
			article.setBlogger(rs.getString("blogger"));
			article.setTitle(rs.getString("title"));
			article.setContent(rs.getString("content"));
			article.setCreationTime(rs.getDate("creationTime"));
			article.setIsReleased(rs.getBoolean("isReleased"));
			article.setReleaseTime(rs.getDate("releaseTime"));
		}
		rs.close();
		state.close();
		return article;
	}

}
